package dbhelper;



import android.content.Context;
import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

/**
 * SQLite帮助类，建表及更新表，打开数据库
 * 
 * @author king
 * 
 */
public class DBOpenHelper extends SQLiteOpenHelper {

	public DBOpenHelper(Context context) {
		super(context, "water.db", null, 1);
	}

	/**
	 * 建表
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		
		//用户信息表
		String userinfosql = "CREATE TABLE userinfo (_id  INTEGER PRIMARY KEY AUTOINCREMENT,usernumber varchar(20), username varchar(20),useradds varchar(20),usertype valchar(20),userstatus valchar(20)"
				+ " ,phone valchar(20),type varchar(20),ismultiplecopy varchar(10))";
		System.out.println(userinfosql+"用户信息");
		db.execSQL(userinfosql);
		
		String waterinfo="create table waterinfo (_id  INTEGER PRIMARY KEY AUTOINCREMENT,waterlistid number,waternumber varchar(20),usernumber varchar(20),alarmlimit number,lowalarmlimit number," +
				"latitude number,longitude number,wateradds varchar(20),RFIDnumber varchar(20),lastmonthwater number,locationadds varchar(30)," +
				"thismonthwater number,metercaliber varchar(20),lastdate varchar(20),thisdate varchar(20),waterproperties varchar(20)" +
				",bandingstatus varchar(10),iscopystatus varchar(10),isupload varchar(10),desc varchar(20),ismultiplecopy varchar(20),sid varchar(20),adds varchar(20),isnorfid varchar(10))";
		
		
		System.out.println(waterinfo+"水表");
	    db.execSQL(waterinfo);
		
		String usewaterinfosql="create table usewaterinfo(_id  INTEGER PRIMARY KEY AUTOINCREMENT,waternumber varchar(20),month varchar(20),watercount varchar(20)," +
				"               ismultiplecopy varchar(10) )";
		System.out.println(usewaterinfosql+"用户历史用水信息");
		db.execSQL(usewaterinfosql);
		
		String engineeringOrderSql="create table engineeringOrder(_id  INTEGER PRIMARY KEY AUTOINCREMENT,waternumber varchar(20),ordertype varchar(20)," +
				                    "desc varchar(100),imagepath varchar(20),videopath varchar(20),soundpath varchar(20),ordername varchar(20),status varchar(20) )";
		System.out.println(engineeringOrderSql+"工程单");
		db.execSQL(engineeringOrderSql);
		
		String ordertypeSql="create table type(_id  INTEGER PRIMARY KEY AUTOINCREMENT,number varchar(20),name varchar(20),status varchar(20))";
		System.out.println(ordertypeSql+"类型");
		db.execSQL(ordertypeSql);
		
		String UserTaskSql="create table usertask(_id  INTEGER PRIMARY KEY AUTOINCREMENT,number varchar(20),name varchar(20),desc varchar(20))";
		System.out.println(UserTaskSql+"任务");
		db.execSQL(UserTaskSql);
		
		String ResourcesSql="create table resources(_id  INTEGER PRIMARY KEY AUTOINCREMENT,otherid number,path varchar(40),type varchar(10),status varchar(10))";
		db.execSQL(ResourcesSql);
		System.out.println(ResourcesSql+"资源");
		
		String waterlistSql="create table waterlist(_id INTEGER PRIMARY KEY AUTOINCREMENT,number varchar(20),name varchar(20))";
		db.execSQL(waterlistSql);
		System.out.println("表册"+waterlistSql);
		
		String otherequipmentSql="create table otherequipment(_id INTEGER PRIMARY KEY AUTOINCREMENT, ep_number varchar(20), ep_type varchar(20),isnorfid varchar(10)" +
				",ep_typename varchar(20),ep_adds varchar(50),ep_locationadds varchar(30),latitude number,longitude number,ep_date varchar(20),isadd varchar(20)," +
				"isupload varchar(20),ep_dess varchar(20),checkstatus varchar(20),ep_checkdesc varchar(50),ep_checktype varchar(50),ep_checkdate varchar(20),RFIDnumber varchar(20)" +
				",lastcheckdate varchar(20),lastcheckresult varchar(20),checkupload varchar(10))";
		db.execSQL(otherequipmentSql);
		System.out.println("其它设备"+otherequipmentSql);
	}

	/**
	 * 当前版本号高于上次创建或升级的版本号，SQLiteOpenHelper会调用onUpdate()方法
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS userinfo");
		db.execSQL("DROP TABLE IF EXISTS waterinfo");
		db.execSQL("DROP TABLE IF EXISTS usewaterinfo");
		db.execSQL("DROP TABLE IF EXISTS engineeringOrder");
		db.execSQL("DROP TABLE IF EXISTS type");
		db.execSQL("DROP TABLE IF EXISTS usertask");
		db.execSQL("DROP TABLE IF EXISTS resources");
		db.execSQL("DROP TABLE IF EXISTS waterlist");
		db.execSQL("DROP TABLE IF EXISTS otherequipment");
		onCreate(db);

	}

}
